package kr.cadis.clms.dao;

import java.io.Reader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import kr.cadis.clms.model.Absence;
import kr.cadis.clms.model.Person;

import com.ibatis.common.resources.Resources;
import com.ibatis.sqlmap.client.SqlMapClient;
import com.ibatis.sqlmap.client.SqlMapClientBuilder;

public class AbsenceDao {
	
	Reader reader;
	SqlMapClient sqlMap;
	
	public AbsenceDao() {
		try {
			reader = Resources.getResourceAsReader("SqlMapConfig.xml");
			sqlMap = SqlMapClientBuilder.buildSqlMapClient(reader);
		}
		catch (Exception ex) {
			System.out.println(ex.getMessage());
		}
	}
	
	public boolean insert(Absence absence) throws SQLException {
		sqlMap.insert("Absence.insert", absence);
		
		if(absence.getNo() == 0) { 
			return false;
		} else {
			return true;
		}	
	}
	
	public boolean delete(int no) throws SQLException {
		if(sqlMap.delete("Absence.delete", no) == 1) { 
			return true;
		} else {
			return false;
		}	
	}
	
	public boolean update(Absence absence) throws SQLException {
		sqlMap.update("Absence.update", absence);
		
		if(absence.getNo() == 0) { 
			return false;
		} else {
			return true;
		}	
	}
	
	public List<Absence> selectByNoPerson(int noPerson) throws SQLException {
		List<Absence> absences = (List<Absence>)sqlMap.queryForList("Absence.selectByNoPerson", noPerson);
		
		return absences;
	}
	
	public Absence selectByPk(int no) {
		Absence absence = null;

		String sql = "select * from absence where no = ?";

		String url="jdbc:mysql://localhost/CLMS?useUnicode=true&characterEncoding=UTF-8";
		Connection con = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;

		try {
			Class.forName("com.mysql.jdbc.Driver");

			con = DriverManager.getConnection(url,"jackshy","zero0055");

			// 1.2.1 ��� ��ü ��
			stmt = con.prepareStatement(sql);

			stmt.setInt(1, no);

			// 1.2.2 ��� ��ü�� sql �� ����			
			rs = stmt.executeQuery();

			rs.next();

			absence = new Absence();

			absence.setNo(rs.getInt("no"));
			absence.setNoPerson(rs.getInt("no_person"));
			absence.setStartDate(rs.getDate("start_date"));
			absence.setFinishDate(rs.getDate("finish_date"));

		} catch(Exception ex) {

		} finally {	
			// 1.2.3 ��� ��ü ����
			try {
				stmt.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block

			}

			// 1.3 DB ���� ����
			try {
				con.close();			
			} catch (SQLException e) {
				// TODO Auto-generated catch block

			}
		}

		return absence;
	}
	
	public Absence selectByFk(int noPerson) {
		Absence absence = null;

		String sql = "select * from absence where no_person = ?";

		String url="jdbc:mysql://localhost/CLMS?useUnicode=true&characterEncoding=UTF-8";
		Connection con = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;

		try {
			Class.forName("com.mysql.jdbc.Driver");

			con = DriverManager.getConnection(url,"jackshy","zero0055");

			// 1.2.1 ��� ��ü ��
			stmt = con.prepareStatement(sql);

			stmt.setInt(1, noPerson);

			// 1.2.2 ��� ��ü�� sql �� ����			
			rs = stmt.executeQuery();

			rs.next();

			absence = new Absence();

			absence.setNo(rs.getInt("no"));
			absence.setNoPerson(rs.getInt("no_person"));
			absence.setStartDate(rs.getDate("start_date"));
			absence.setFinishDate(rs.getDate("finish_date"));

		} catch(Exception ex) {

		} finally {	
			// 1.2.3 ��� ��ü ����
			try {
				stmt.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block

			}

			// 1.3 DB ���� ����
			try {
				con.close();			
			} catch (SQLException e) {
				// TODO Auto-generated catch block

			}
		}

		return absence;
	}
	
	public int selectAbsenceCountByDate(String strTheDate) throws SQLException {		
		return (Integer) sqlMap.queryForObject("Absence.selectAbsenceCountByDate", strTheDate);
	}
	
/*	public List<Absence> selectAll() {
		String sql = "select * from absence";
		String url="jdbc:mysql://localhost/clms?useUnicode=true&characterEncoding=UTF-8";
		Connection con = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		List<Absence> absences = new ArrayList<Absence>();
		try {
			Class.forName("com.mysql.jdbc.Driver");

			con = DriverManager.getConnection(url,"jackshy","zero0055");
			stmt = con.prepareStatement(sql);
			rs = stmt.executeQuery();
			while(rs.next()) {
				Absence absence = new Absence();
				absence.setNoPerson(rs.getInt("no_person"));
				absence.setStartDate(rs.getDate("start_Date"));
				absence.setFinishDate(rs.getDate("finish_Date"));
				absences.add(absence);				
			}
		}
		catch(Exception ex) {
		}
		finally {	
			try {
				rs.close();
			}
			catch(Exception ex) {
			}
			try {			
				stmt.close();
			}
			catch(Exception ex) {
			}
			try {			
				con.close();	
			}
			catch(Exception ex) {
			}
		}
		return absences;
	}*/
}
